package admin.dao.impl;

import admin.bean.Express;
import admin.dao.ExpressDao;
import exception.ReCodeException;
import util.DBUtil;
import util.RandomUtil;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

public class ExpressDaoImpl extends DBUtil implements ExpressDao {

    //查询数据库中的全部快递（总数+新增），待取件的快递（总数+新增）
    public static final String SQL_CONSOLE = " select count(id) data1_num," +
            " count(to_days(intime)=to_days(now()) or null) data1_newinto," +
            " count(status=0 or null) data2_num ," +
            " count(to_days(intime)=to_days(now()) and status=0 or null) data2_newinto " +
            " from express ";
    //查询数据库中的所有快递信息
    public static final String SQL_FIND_ALL = " select * from express ";
    //通过快递主键查询快递信息
    public static final String SQL_FIND_BY_ID = " select * from express where id=? ";
    //分页查询数据库中的快递信息
    public static final String SQL_FIND_LIMIT = " select * from express limit ?,? ";
    //通过取件码查询
    public static final String SQL_FIND_BY_CODE = " select * from express where code=? ";
    //通过快递单号查询快递信息
    public static final String SQL_FIND_BY_NUMBER = " select * from express where number=? ";
    //通过收件人手机号查询快递信息
    public static final String SQL_FINF_BY_USER_PHONE = " select * from express where userphone=? ";
    //通过配送员手机号查询快递信息
    public static final String SQL_FIND_BY_SYSPHONE = " select * from express where sysphone=? ";
    //录入快递
    public static final String SQL_INSERT = " insert into express ( number, username, userphone, company, code, intime,status, sysphone) values(?,?,?,?,?,now(),0,?) ";
    //修改快递信息
    public static final String SQL_UPDATE = " update express set number=?,username=?,userphone=?,company=?,status=? where id=? ";
    //通过取件码更新快递的状态码（取件）
    public static final String SQL_UPDATE_STATUS = " update express set status=1,outtime=now(),code=null where code=? ";
    //通过快递主键id删除快递信息
    public static final String SQL_DELETE = " delete from express where id=? ";


    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String,Integer>> mapList = new ArrayList<>();

        try {
            //获取结果集对象
            resultSet = query(SQL_CONSOLE);
            while (resultSet.next()){
                //从数据库中获取总快递数
                int data1_num = resultSet.getInt("data1_num");
                //从数据库中获取今日新增快递件数
                int data1_newinto = resultSet.getInt("data1_newinto");
                //从数据库中获取待取件总数
                int data2_num = resultSet.getInt("data2_num");
                //从数据库中获取今日新增待取件数
                int data2_newinto = resultSet.getInt("data2_newinto");

                //数据通过键值对方式保存
                Map data1 = new HashMap();

                data1.put("data1_num",data1_num);
                data1.put("data1_newinto",data1_newinto);

                Map data2 = new HashMap();
                data2.put("data2_num",data2_num);
                data2.put("data2_newinto",data2_newinto);

                //将数据放进集合中保存起来
                mapList.add(data1);
                mapList.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return mapList;
    }

    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        List<Express> list = new ArrayList<>();

        try {
            //limit为true分页查询快递信息
            if (limit){
                List params = new ArrayList();
                params.add(offset);
                params.add(pageNumber);
                resultSet = query(SQL_FIND_LIMIT,params);

            }else {//limit为false查询所有快递信息
                resultSet = query(SQL_FIND_ALL);
            }

            while (resultSet.next()){
                //获取数据库数据
                int id = resultSet.getInt("id");
                String number = resultSet.getString("number");
                String userName = resultSet.getString("username");
                String userPhone = resultSet.getString("userphone");
                String company = resultSet.getString("company");
                String code = resultSet.getString("code");
                Timestamp inTime = resultSet.getTimestamp("intime");
                Timestamp outTime = resultSet.getTimestamp("outtime");
                int status = resultSet.getInt("status");
                String sysPhone = resultSet.getString("sysphone");
                //将信息存储于快递对象中
                Express express = new Express(id,number,userName,userPhone,company,code,inTime,outTime,status,sysPhone);
                //将快递对象放入集合
                list.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return list;
    }

    @Override
    public Express findById(int id) {
        List params = new ArrayList();
        params.add(id);
        Express express = new Express();

        try {
            resultSet = query(SQL_FIND_BY_ID,params);
            while (resultSet.next()){
                //将查询的快递数据存储到快递对象中
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return express;
    }

    @Override
    public Express findByNumber(String number) {
        ArrayList params = new ArrayList();
        params.add(number);
        Express express = new Express();

        try {
            resultSet = query(SQL_FIND_BY_NUMBER,params);
            while (resultSet.next()){
                //将查询的快递数据存储到快递对象中
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return express;
    }

    @Override
    public Express findByCode(String code) {
        ArrayList params = new ArrayList();
        params.add(code);
        Express express = new Express();

        try {
            resultSet = query(SQL_FIND_BY_CODE,params);
            while (resultSet.next()){
                //将查询的快递数据存储到快递对象中
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return express;
    }

    @Override
    public List<Express> findByUserPhone(String userPhone) {
        List<Express> expressList = new ArrayList<>();
        ArrayList params = new ArrayList();
        params.add(userPhone);

        try {
            resultSet = query(SQL_FINF_BY_USER_PHONE,params);

            while (resultSet.next()){
                //每循环一次生成一个新的快递对象，并存储数据库中的信息
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));

                expressList.add(express);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return expressList;
    }

    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> expressList = new ArrayList<>();
        ArrayList params = new ArrayList();
        params.add(sysPhone);

        try {
            resultSet = query(SQL_FIND_BY_SYSPHONE,params);

            while (resultSet.next()){
                //每循环一次生成一个新的快递对象，并存储数据库中的信息
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));

                expressList.add(express);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return expressList;
    }

    @Override
    public boolean insert(Express express) {
        List params = new ArrayList();
        params.add(express.getNumber());
        params.add(express.getUserName());
        params.add(express.getUserPhone());
        params.add(express.getCompany());
        //调用随机生成一个6位数的快递取件码的工具类
        //String code = express.getCode();
        RandomUtil c = new RandomUtil();
        String code = c.getCode();
        express.setCode(code);

        params.add(code);   //生成取件码
        params.add(express.getSysPhone());

        try {
            int rows = update(SQL_INSERT,params);
            //此处简化了三目运算符rows>0?true:false
            return rows > 0;
        } catch (ReCodeException e) {
            //e.printStackTrace();
            System.out.println("取件码重复的异常被捕获到了！");
        } finally {
            closeAll();
        }

        return false;
    }

    @Override
    public boolean update(int id, Express newExpress) {
        List params = new ArrayList();
        params.add(newExpress.getNumber());
        params.add(newExpress.getUserName());
        params.add(newExpress.getUserPhone());
        params.add(newExpress.getCompany());
        params.add(newExpress.getStatus());
        params.add(id);

        try {
            int rows = update(SQL_UPDATE,params);
            if (rows>0){
                //重传入主键id给快递对象
                newExpress.setId(id);
                return true;
            }
            return false;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return false;
    }

    @Override
    public boolean updateStatus(String code) {
        List params = new ArrayList();
        params.add(code);

        try {
            int rows = update(SQL_UPDATE_STATUS,params);

            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        List params = new ArrayList();
        params.add(id);

        try {
            int rows = update(SQL_DELETE,params);
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return false;
    }
}
